﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using PMPUtils.DB;
using PMPUtils.Configuration;
using PMPUtils.Util;

namespace PMPUtils.Report
{
    public class BurndownOperator
    {
        /// <summary>
        /// Return true if NF1 burndown is already initialized
        /// </summary>
        /// <param name="sprint"></param>
        /// <returns></returns>
        public static bool IsNF1BurndownInitialized(String sprint)
        {
            String strQuery = "select COUNT(id) exist from burndown where sprint = '" + sprint + "' and team = 'NF1'";
            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtNF1Initialized = dbOperator.Read(strQuery);

            bool isNF1BurndownInitialized = false;
            foreach (DataRow row in dtNF1Initialized.Rows)
            {
                if (row["exist"] != DBNull.Value)
                {
                    if (Convert.ToInt32(row["exist"]) >= 1)
                    {
                        isNF1BurndownInitialized = true;
                    }
                }
            }

            return isNF1BurndownInitialized;
        }
        
        /// <summary>
        /// Return true if NF2 burndown is initialized
        /// </summary>
        /// <param name="sprint"></param>
        /// <returns></returns>
        public static bool IsNF2BurndownInitialized(String sprint)
        {
            String strQuery = "select COUNT(id) exist from burndown where sprint = '" + sprint + "' and team = 'NF2'";
            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtNF2Initialized = dbOperator.Read(strQuery);

            bool isNF2BurndownInitialized = false;
            foreach (DataRow row in dtNF2Initialized.Rows)
            {
                if (row["exist"] != DBNull.Value)
                {
                    if (Convert.ToInt32(row["exist"]) >= 1)
                    {
                        isNF2BurndownInitialized = true;
                    }
                }
            }

            return isNF2BurndownInitialized;
        }

        /// <summary>
        /// Initialize NF1 burndown capacity
        /// </summary>
        /// <param name="sprint"></param>
        public static void InitializeNF1Burndown(String sprint)
        {
            double NF1Capacity = SprintOperator.ReadNF1Capacity(sprint);

            String strInsertCapacity = String.Empty;
            String strInsertBurndown = String.Empty;
            String strInsertEVM = String.Empty;
            if (!IsNF1BurndownInitialized(sprint))
            {
                // Insert capacity
                strInsertCapacity = "insert into burndown (sprint, team, type";

                for (int i = 1; i <= 10; i++)
                {
                    strInsertCapacity += ", weekday" + i.ToString().PadLeft(2, '0');
                }

                strInsertCapacity += " ) ";
                strInsertCapacity += " values ('" + sprint + "', 'NF1', 'CAPACITY'";

                for (int i = 1; i <= 10; i++)
                {
                    strInsertCapacity += ", " + NF1Capacity * (10 - i + 1) / 10;
                }

                strInsertCapacity += " ) ";

                // Insert burndown with 0.0
                strInsertBurndown = "insert into burndown (sprint, team, type";

                for (int i = 1; i <= 10; i++)
                {
                    strInsertBurndown += ", weekday" + i.ToString().PadLeft(2, '0');
                }

                strInsertBurndown += " ) ";
                strInsertBurndown += " values ('" + sprint + "', 'NF1', 'WORKLOAD'";

                for (int i = 1; i <= 10; i++)
                {
                    strInsertBurndown += ", 0.0";
                }

                strInsertBurndown += " ) ";

                // Insert EVM with 0.0
                strInsertEVM = "insert into burndown (sprint, team, type";

                for (int i = 1; i <= 10; i++)
                {
                    strInsertEVM += ", weekday" + i.ToString().PadLeft(2, '0');
                }

                strInsertEVM += " ) ";
                strInsertEVM += " values ('" + sprint + "', 'NF1', 'WORKLOAD_EVM'";

                for (int i = 1; i <= 10; i++)
                {
                    strInsertEVM += ", 0.0";
                }

                strInsertEVM += " ) ";
            }

            if (!strInsertCapacity.Equals(String.Empty))
            {
                DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
                dbOperator.Update(strInsertCapacity);
            }

            if (!strInsertBurndown.Equals(String.Empty))
            {
                DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
                dbOperator.Update(strInsertBurndown);
            }

            if (!strInsertEVM.Equals(String.Empty))
            {
                DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
                dbOperator.Update(strInsertEVM);
            }
        }

        /// <summary>
        /// Initialize the NF2 burndown
        /// </summary>
        /// <param name="sprint"></param>
        public static void InitializeNF2Burndown(String sprint)
        {
            double NF2Capacity = SprintOperator.ReadNF2Capacity(sprint);

            String strInsert = String.Empty;
            String strInsertBurndown = String.Empty;
            String strInsertEVM = String.Empty;

            if (!IsNF2BurndownInitialized(sprint))
            {
                strInsert = "insert into burndown (sprint, team, type";

                for (int i = 1; i <= 10; i++)
                {
                    strInsert += ", weekday" + i.ToString().PadLeft(2, '0');
                }

                strInsert += " ) ";
                strInsert += " values ('" + sprint + "', 'NF2', 'CAPACITY'";

                for (int i = 1; i <= 10; i++)
                {
                    strInsert += ", " + NF2Capacity * (10 - i + 1) / 10;
                }

                strInsert += " ) ";

                // Insert burndown with 0.0
                strInsertBurndown = "insert into burndown (sprint, team, type";

                for (int i = 1; i <= 10; i++)
                {
                    strInsertBurndown += ", weekday" + i.ToString().PadLeft(2, '0');
                }

                strInsertBurndown += " ) ";
                strInsertBurndown += " values ('" + sprint + "', 'NF2', 'WORKLOAD'";

                for (int i = 1; i <= 10; i++)
                {
                    strInsertBurndown += ", 0.0";
                }

                strInsertBurndown += " ) ";

                // Insert EVM with 0.0
                strInsertEVM = "insert into burndown (sprint, team, type";

                for (int i = 1; i <= 10; i++)
                {
                    strInsertEVM += ", weekday" + i.ToString().PadLeft(2, '0');
                }

                strInsertEVM += " ) ";
                strInsertEVM += " values ('" + sprint + "', 'NF2', 'WORKLOAD_EVM'";

                for (int i = 1; i <= 10; i++)
                {
                    strInsertEVM += ", 0.0";
                }

                strInsertEVM += " ) ";
            }

            // Execute inserting capacity
            if (!strInsert.Equals(String.Empty))
            {
                DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
                dbOperator.Update(strInsert);
            }

            // Execute inserting burndown
            if (!strInsertBurndown.Equals(String.Empty))
            {
                DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
                dbOperator.Update(strInsertBurndown);
            }

            // Execute inserting burndown
            if (!strInsertEVM.Equals(String.Empty))
            {
                DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
                dbOperator.Update(strInsertEVM);
            }
        }

        /// <summary>
        /// Update NF1's burndown chart based on the remaining estimations.
        /// </summary>
        /// <param name="sprint"></param>
        public static void UpdateNF1Burndown(String sprint)
        {
            String weekday = SprintOperator.ReadSprintOffsetString(sprint);
            String strUpdate = "update burndown " +
                " set " + weekday + " = (select sum(case when task_id is null or task_id = '' then isnull(orig_est, 0)-isnull(actual, 0) else rem_est end) rem_est from goals where sprint = " + sprint + " and team = 'NF1' and rev is null) " +
                " where sprint = " + sprint + " and team = 'NF1' and type = 'WORKLOAD'";
            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            dbOperator.Update(strUpdate);
        }

        /// <summary>
        /// Update NF2's burndown chart based on the remaining estimations.
        /// </summary>
        /// <param name="sprint"></param>
        public static void UpdateNF2Burndown(String sprint)
        {
            String weekday = SprintOperator.ReadSprintOffsetString(sprint);
            String strUpdate = "update burndown " +
                " set " + weekday + " = (select sum(case when task_id is null or task_id = '' then isnull(orig_est, 0)-isnull(actual, 0) else rem_est end) rem_est from goals where sprint = " + sprint + " and team = 'NF2' and rev is null) " +
                " where sprint = " + sprint + " and team = 'NF2' and type = 'WORKLOAD'";
            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            dbOperator.Update(strUpdate);
        }

        /// <summary>
        /// Update the ETC value for NF1
        /// </summary>
        /// <param name="sprint"></param>
        public static void UpdateNF1EVM(String sprint)
        {
            String weekday = SprintOperator.ReadSprintOffsetString(sprint);
            String strUpdate = "update burndown " +
                " set " + weekday + " = (select isnull(sum(case when ETC is null then rem_est when ETC = 0.0 then rem_est else ETC end), 0.0) ETC from goals where sprint = " + sprint + " and team = 'NF1' and rev is null) " +
                " where sprint = " + sprint + " and team = 'NF1' and type = 'WORKLOAD_EVM'";
            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            dbOperator.Update(strUpdate);
        }

        public static void UpdateNF2EVM(String sprint)
        {
            String weekday = SprintOperator.ReadSprintOffsetString(sprint);
            String strUpdate = "update burndown " +
                " set " + weekday + " = (select isnull(sum(case when ETC is null then rem_est when ETC = 0.0 then rem_est else ETC end), 0.0) ETC from goals where sprint = " + sprint + " and team = 'NF2' and rev is null) " +
                " where sprint = " + sprint + " and team = 'NF2' and type = 'WORKLOAD_EVM'";
            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            dbOperator.Update(strUpdate);
        }

        public static void UpdateBurndown(String sprint)
        {
            // Initialize
            InitializeNF1Burndown(sprint);
            InitializeNF2Burndown(sprint);

            // Update burndown workload data
            UpdateNF1Burndown(sprint);
            UpdateNF2Burndown(sprint);

            // Update EVN data
            UpdateNF1EVM(sprint);
            UpdateNF2EVM(sprint);
        }

        public static void RefreshGridView(String sprint, GridView grvBurndown)
        {
            String strQuery = "SELECT [team], [type], [weekday01], [weekday02], [weekday03], [weekday04], [weekday05], [weekday06], [weekday07], [weekday08], [weekday09], [weekday10] FROM [burndown] where sprint = " + sprint + " ORDER BY [team], [type]";

            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtBurndown = dbOperator.Read(strQuery);

            grvBurndown.DataSource = dtBurndown.DefaultView;
            grvBurndown.DataBind();
        }
    }
}
